package DAO;

import java.sql.*;
import connect.ConnectDB;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;
import javax.swing.JComboBox;

public class JobDAO {

    Connection conn;
    ResultSet rsJob = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;
    TreeMap<Integer, String> tmDep;
    TreeMap<String, String> tmSec;

    public JobDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataJob();
    }

    public void loadDataJob() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             JobRotation";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsJob = st.executeQuery(query);
        } catch (Exception e) {
            System.out.println("Error in loadDataJob(): " + e.getMessage());
        }
    }

    public String checkJobInputCode(String empId, String select) {
        try {
            // Confirm Vac Fill Details
            if (select.equals("Confirm")) {
                String query = "Select * from JobRotation where EMPID = ? and STATUS = 0";
                PreparedStatement pst = conn.prepareStatement(query);
                pst.setString(1, empId);
                ResultSet rs = pst.executeQuery();

                if (rs.next()) {
                    return "Not confirm";
                } else {
                    return "Not found";
                }
            }

            // Emergency
            if (select.equals("Emergency")) {
                String query = "SELECT EMPID FROM Employees";
                Statement st = conn.createStatement();
                ResultSet rs = st.executeQuery(query);

                while (rs.next()) {
                    if (rs.getString("EMPID").trim().equals(empId)) {
                        return "Found";
                    }
                }
                return "Not found";
            }

            return "Not Found";
        } catch (Exception e) {
            System.out.println("Error in checkJobInputCode: " + e.getMessage());
            return "Error";
        }
    }

    public Vector loadVacToConfirm(String empId) {
        try {
            Vector v = new Vector();
            String desName = "";
            String depName = "";

            String query = "select * from JobRotation as J inner Join Employees as e On J.EMPID = E.EMPID AND J.EMPID = ? and J.STATUS = 0 inner JOIN Departments AS d ON E.DEPTNO = d.DEPTNO";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                desName = rs.getString("PRESENTDESIGNATION");
                depName = rs.getString("DEPTNO") + " - " + rs.getString("DEPTNAME");
                v.add(desName);
                v.add(depName);
                return v;
            }
            return null;
        } catch (Exception e) {
            System.out.println("Error in loadVacToConfirm: " + e.getMessage());
            return null;
        }
    }

    public void loadDataDep() {
        try {
            tmDep = new TreeMap<Integer, String>();

            String query = "select * from Departments";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);

            while (rs.next()) {
                tmDep.put(rs.getInt("DEPTNO"), rs.getString("DEPTNAME"));
            }

        } catch (Exception e) {
            System.out.println("Error in loadDataDep: " + e.getMessage());
        }
    }

    public void loadDataSec(int depNo) {
        try {
            tmSec = new TreeMap<String, String>();

            String query = "select * from Sections where DEPTNO = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setInt(1, depNo);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                tmSec.put(rs.getString("SECTID"), rs.getString("SECTNAME"));
            }

        } catch (Exception e) {
            System.out.println("Error in loadDataSec: " + e.getMessage());
        }
    }

    public String loadPresentDesign(String empId) {
        try {
            String query = "select * from Employees as E Join Designation as D ON E.DESIGNID = D.DESIGNID AND EMPID = ?";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();
            
            while (rs.next()) {
                return rs.getString("DESIGNATION");
            }
            
            return "";
        } catch (Exception e) {
            System.out.println("Error in loadPresentDesign: " + e.getMessage());
            return "";
        }
    }
    
    public boolean insertConfirmVac(Vector v) {
        try {
            String empId = v.get(0).toString();
            String status = v.get(1).toString();
            String remark = v.get(2).toString();

            String query = "{call Job_Update_ConfirmVacFill(?,?,?)}";
            CallableStatement cst = conn.prepareCall(query);
            cst.setString(1, empId);
            cst.setString(2, status);
            cst.setString(3, remark);

            if (cst.executeUpdate() != -1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in insertConfirmVac: " + e.getMessage());
            return false;
        }
    }

    public String getDes(String empId) {
        try {
            String query = "select * from Employees as e join Designation as d on EMPID = ? and e.DESIGNID = d.DESIGNID";
            pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();
            rs.next();
            return rs.getString("DESIGNATION");
        } catch (Exception e) {
            System.out.println("Error in getDes: " + e.getMessage());
            return "";
        }
    }

    public Vector getDepNoVac() {
        try {
            Vector vDepNo = new Vector();
            Vector vDepName = new Vector();
            String query = "SELECT * FROM Departments";
            pst = conn.prepareStatement(query);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                vDepNo.add(rs.getString("DEPTNO"));
                vDepName.add(rs.getString("DEPTNAME"));
            }
            pst.close();
            rs.close();
            return vDepNo;
        } catch (Exception e) {
            System.out.println("Error in getDep: " + e.getMessage());
            return null;
        }
    }

    public String getDepWithVac(String vacId, String empId) {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Vacancies as V "
                    + "     JOIN "
                    + "             VacancyFillDetails AS VF "
                    + "     ON "
                    + "             V.VACANCYID = VF.VACANCYID "
                    + "     AND "
                    + "             VF.VACANCYID = ? "
                    + "     AND "
                    + "             VF.EMPID = ? "
                    + "     JOIN "
                    + "             Departments AS D "
                    + "     ON "
                    + "             V.DEPTNO = D.DEPTNO";
            pst = conn.prepareStatement(query);
            pst.setString(1, vacId);
            pst.setString(2, empId);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                return rs.getString("DEPTNO");
            }
            return "";
        } catch (Exception e) {
            System.out.println("Error in getDepWithVac: " + e.getMessage());
            return "";
        }
    }

    public String getVac(String empId) {
        try {
            String query = "select * from VacancyFillDetails  where EMPID = ?";
            pst = conn.prepareStatement(query);
            pst.setString(1, empId);
            ResultSet rs = pst.executeQuery();
            rs.next();
            return rs.getString("VACANCYID");
        } catch (Exception e) {
            System.out.println("Error in getVac: " + e.getMessage());
            return null;
        }
    }

    public void loadCbDep(JComboBox cbDep) {
        cbDep.removeAllItems();
        cbDep.addItem("Please select...");
        
        loadDataDep();
        Set<Integer> key = tmDep.keySet();
        for (Integer i : key) {
            String dep = i + " - " + tmDep.get(i);
            cbDep.addItem(dep);
        }
    }
    
    public void loadCbSec (JComboBox cbSec, int depNo) {
        cbSec.removeAllItems();
        cbSec.addItem("Please select...");
        
        loadDataSec(depNo);
        Set<String> key = tmSec.keySet();
        for (String str : key) {
            String sec = str + " - " + tmSec.get(str);
            cbSec.addItem(sec);
        }
    }

    public boolean insertJob(Vector v) {
        try {
            String empId = v.get(0).toString();
            String desName = v.get(1).toString();
            int depNo = Integer.parseInt(v.get(2).toString().trim());
            String secId = v.get(3).toString();
            String remark = v.get(3).toString();

            String query = "{call insJob_Rotation(?,?,?,?,?)}";
            CallableStatement cst = conn.prepareCall(query);
            cst.setString(1, empId);
            cst.setString(2, desName);
            cst.setInt(3, depNo);
            cst.setString(4, secId);
            cst.setString(5, remark);

            if (cst.executeUpdate() != -1) {
                return true;
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in insertJob: " + e.getMessage());
            return false;
        }
    }
    
    public int getDepNoFromEmpId(String empId) {
        try {
            
            return 0;
        } catch (Exception e) {
            System.out.println("Error in getDepNoFromEmpId");
            return 0;
        }
    }
    
    public void closeConnect() {
        try {
            if (rsJob != null) {
                rsJob.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }

            if (cdb.conn != null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Job Rotation");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
